from pathlib import Path
from typing import List, Dict, Any

import pymysql
import yaml
from dbutils.pooled_db import PooledDB
# DBUtils==3.1.0
# Flask==3.1.1
# PyMySQL==1.1.1
# PyYAML==6.0.2


def load_config() -> Dict[str, Any]:
    """加载配置文件"""
    config_path = Path(__file__).parent / 'config.yaml'
    try:
        with open(config_path, 'r', encoding='utf-8') as f:
            config = yaml.safe_load(f)
        return config['database']
    except Exception as e:

        print(f"配置文件加载失败: {str(e)}")
        return {"path": str(Path.cwd())}


class MySQL:
    def __init__(self, host: str = 'localhost',
                 user: str = 'root',
                 password: str = '44448888',
                 database: str = 'library',
                 port: int = 3306,
                 charset: str = 'utf8'):
        """初始化MySQL数据库连接"""
        self.config = {
            'host': host,
            'user': user,
            'password': password,
            'database': database,
            'port': port,
            'charset': charset,
        }
        self.conn = None
        self.cursor = None
        self.status = False

    def connect(self) -> bool:
        """建立数据库连接"""
        try:
            # self.conn = pymysql.connect(**self.config)
            self.conn = POOL.connection()  # 连接池访问
            self.cursor = self.conn.cursor(pymysql.cursors.DictCursor)
            self.status = True
            return True
        except Exception as e:
            print(f"数据库连接失败: {str(e)}")
            return False

    def query(self, cmd: str, params: tuple = None) -> List[Dict[str, Any]]:
        """执行查询操作"""
        try:
            self.cursor.execute(cmd, params)
            return self.cursor.fetchall()
        except Exception as e:
            print(f"查询执行失败: {str(e)}")
            return []

    def update(self, cmd: str, params: tuple = None) -> bool:
        """执行更新操作"""
        try:
            print(cmd)
            print(params)
            self.cursor.execute(cmd, params)
            self.conn.commit()
            return True
        except Exception as e:
            print(f"更新执行失败: {str(e)}")
            self.conn.rollback()
            return False

    def close(self):
        """关闭数据库连接"""
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.status = False
            self.conn.close()


# 加载数据库配置
CONFIG = load_config()
POOL = PooledDB(
    creator=pymysql,
    maxconnections=10,  # 允许的最大连接数，0和None表示不限制连接数
    mincached=2,  # 最少的空闲链接数，0表示不创建
    maxcached=5,  # 最大限制的链接数，0和None不限制
    blocking=True,  # 没有可用连接时，是否阻塞等待
    setsession=[],
    ping=0,
    **CONFIG
)

if __name__ == '__main__':
    pass
